package com.datamation.swadeshi.control.data;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.datamation.swadeshi.model.ItemFreeIssue;
import com.datamation.swadeshi.model.Items;

public class ItemsDS {

	
	private SQLiteDatabase dB;
	private DatabaseHelper dbHelper;
	Context context;
	private String TAG="swadeshi";
	
	
	public ItemsDS (Context context){
		this.context = context;
		dbHelper = new DatabaseHelper(context);
	}
	
	public void open() throws SQLException {
		dB = dbHelper.getWritableDatabase();
	}

	@SuppressWarnings("static-access")
	public int createOrUpdateItems(ArrayList<Items>  list) {
		
		int count =0;
		
		if(dB == null){
			open();
		}else if(!dB.isOpen()){
			open();
		}
		Cursor cursor = null;
		
	try{
		
//		cursor = dB.rawQuery("SELECT * FROM " + dbHelper.TABLE_FITEM, null);
//		if(cursor.getCount()>0){
//			int success = dB.delete(dbHelper.TABLE_FITEM, null, null);
//			Log.v("Success", success+"");
//		}
			
			
    for (Items item : list) {
		
			ContentValues values = new ContentValues();
			
			values.put(dbHelper.FITEM_ADD_MATCH, item.getFITEM_ADD_MATCH());
			values.put(dbHelper.FITEM_ADD_USER, item.getFITEM_ADD_USER());
			values.put(dbHelper.FITEM_AVG_PRICE, item.getFITEM_AVG_PRICE());
			values.put(dbHelper.FITEM_BRAND_CODE, item.getFITEM_BRAND_CODE());
			values.put(dbHelper.FITEM_GROUP_CODE, item.getFITEM_GROUP_CODE());
			values.put(dbHelper.FITEM_ITEM_CODE, item.getFITEM_ITEM_CODE());
			values.put(dbHelper.FITEM_ITEM_NAME, item.getFITEM_ITEM_NAME());
			values.put(dbHelper.FITEM_ITEM_STATUS, item.getFITEM_ITEM_STATUS());
			values.put(dbHelper.FITEM_MAP_CODE, item.getFITEM_MAP_CODE());
			values.put(dbHelper.FITEM_MUST_SALE, item.getFITEM_MUST_SALE());
			values.put(dbHelper.FITEM_NOU_CASE, item.getFITEM_NOU_CASE());
			values.put(dbHelper.FITEM_ORD_SEQ, item.getFITEM_ORD_SEQ());
			values.put(dbHelper.FITEM_PRIL_CODE, item.getFITEM_PRIL_CODE());
			values.put(dbHelper.FITEM_RE_ORDER_LVL, item.getFITEM_RE_ORDER_LVL());
			values.put(dbHelper.FITEM_RE_ORDER_QTY, item.getFITEM_RE_ORDER_QTY());
			values.put(dbHelper.FITEM_S_BRAND_CODE, item.getFITEM_S_BRAND_CODE());
			values.put(dbHelper.FITEM_SKU_CODE, item.getFITEM_SKU_CODE());
			values.put(dbHelper.FITEM_SKU_SIZ_CODE, item.getFITEM_SKU_SIZ_CODE());
			values.put(dbHelper.FITEM_TAX_COM_CODE, item.getFITEM_TAX_COM_CODE());
			values.put(dbHelper.FITEM_TYPE_CODE, item.getFITEM_TYPE_CODE());
			values.put(dbHelper.FITEM_UNIT_CODE, item.getFITEM_UNIT_CODE());
			values.put(dbHelper.FITEM_VEN_P_CODE, item.getFITEM_VEN_P_CODE());
			
			count = (int) dB.insert(dbHelper.TABLE_FITEM, null, values);

			}
		}catch (Exception e) {
		
			Log.v(TAG+" Exception", e.toString());
	
		}finally {  
			if (cursor !=null) {
				cursor.close();
			}
			dB.close();
		}
		return count;
				
	}
	
	public int deleteAll(){
		
		int count =0;
		
		if(dB == null){
			open();
		}else if(!dB.isOpen()){
			open();
		}
		Cursor cursor = null;
		try{
			
			cursor = dB.rawQuery("SELECT * FROM " + dbHelper.TABLE_FITEM, null);
			count =cursor.getCount();
			if(count>0){
				int success = dB.delete(dbHelper.TABLE_FITEM, null, null);
				Log.v("Success", success+"");
			}
		}catch (Exception e){

			Log.v(TAG+" Exception", e.toString());
			
		}finally{  
			if (cursor !=null) {
				cursor.close();
			}
			dB.close();
		}
		
		return count;
		
	}
	
	
	@SuppressWarnings("static-access")
	public ArrayList<Items> getAllItem(String newText, String type, String refno, String LocCode) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		ArrayList<Items> list = new ArrayList<Items>();
		
		//String selectQuery = "SELECT * FROM "+dbHelper.TABLE_FITEM +" WHERE "+dbHelper.FITEM_ITEM_CODE+" || "+dbHelper.FITEM_ITEM_NAME +" LIKE '%"+newText+"%' AND "+dbHelper.FITEM_ITEM_CODE+" not in (SELECT DISTINCT itemcode FROM forddet WHERE "+type+" And refno ='"+refno+"')";
		
		String selectQuery = "SELECT itm.* , loc.QOH FROM fitem itm, fitemLoc loc WHERE itm.ItemCode || itm.ItemName LIKE '%"+newText+"%' AND loc.itemcode=itm.itemcode AND  loc.LocCode='"+LocCode+"' AND itm.ItemCode not in (SELECT DISTINCT itemcode FROM forddet WHERE "+type+" And refno ='"+refno+"') order by CAST(loc.QOH AS FLOAT) DESC";
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		while(cursor.moveToNext()){
			
			Items items=new Items();
		
			items.setFITEM_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ID)));
			items.setFITEM_ADD_MATCH(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ADD_MATCH)));
			items.setFITEM_ADD_USER(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ADD_USER)));
			items.setFITEM_AVG_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_AVG_PRICE)));
			items.setFITEM_BRAND_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_BRAND_CODE)));
			items.setFITEM_GROUP_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_GROUP_CODE)));
			items.setFITEM_ITEM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ITEM_CODE)));
			items.setFITEM_ITEM_NAME(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ITEM_NAME)));
			items.setFITEM_ITEM_STATUS(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ITEM_STATUS)));
			items.setFITEM_MAP_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_MAP_CODE)));
			items.setFITEM_MUST_SALE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_MUST_SALE)));
			items.setFITEM_NOU_CASE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_NOU_CASE)));
			items.setFITEM_ORD_SEQ(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ORD_SEQ)));
			items.setFITEM_PRIL_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_PRIL_CODE)));
			items.setFITEM_RE_ORDER_LVL(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_RE_ORDER_LVL)));
			items.setFITEM_RE_ORDER_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_RE_ORDER_QTY)));
			items.setFITEM_S_BRAND_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_S_BRAND_CODE)));
			items.setFITEM_SKU_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_SKU_CODE)));
			items.setFITEM_SKU_SIZ_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_SKU_SIZ_CODE)));
			items.setFITEM_TAX_COM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_TAX_COM_CODE)));
			items.setFITEM_TYPE_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_TYPE_CODE)));
			items.setFITEM_UNIT_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_UNIT_CODE)));
			items.setFITEM_VEN_P_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_VEN_P_CODE)));
			items.setQOH(cursor.getString(cursor.getColumnIndex(dbHelper.FITEMLOC_QOH)));
			
			list.add(items);
			
		}
		
		return list;
	}
	
	@SuppressWarnings("static-access")
	public ArrayList<Items> getAllItemForVanSale(String newText, String type, String refno, String LocCode) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		ArrayList<Items> list = new ArrayList<Items>();
		
		//String selectQuery = "SELECT * FROM "+dbHelper.TABLE_FITEM +" WHERE "+dbHelper.FITEM_ITEM_CODE+" || "+dbHelper.FITEM_ITEM_NAME +" LIKE '%"+newText+"%' AND "+dbHelper.FITEM_ITEM_CODE+" not in (SELECT DISTINCT itemcode FROM finvdet WHERE "+type+" And refno ='"+refno+"')";
		
		String selectQuery = "SELECT itm.* , loc.QOH FROM fitem itm, fitemLoc loc WHERE itm.ItemCode || itm.ItemName LIKE '%"+newText+"%' AND loc.itemcode=itm.itemcode AND  loc.LocCode='"+LocCode+"' AND itm.ItemCode not in (SELECT DISTINCT itemcode FROM finvdet WHERE "+type+" And refno ='"+refno+"') order by CAST(loc.QOH AS FLOAT) DESC";
		
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		while(cursor.moveToNext()){
			
			Items items=new Items();
		
			items.setFITEM_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ID)));
			items.setFITEM_ADD_MATCH(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ADD_MATCH)));
			items.setFITEM_ADD_USER(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ADD_USER)));
			items.setFITEM_AVG_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_AVG_PRICE)));
			items.setFITEM_BRAND_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_BRAND_CODE)));
			items.setFITEM_GROUP_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_GROUP_CODE)));
			items.setFITEM_ITEM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ITEM_CODE)));
			items.setFITEM_ITEM_NAME(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ITEM_NAME)));
			items.setFITEM_ITEM_STATUS(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ITEM_STATUS)));
			items.setFITEM_MAP_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_MAP_CODE)));
			items.setFITEM_MUST_SALE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_MUST_SALE)));
			items.setFITEM_NOU_CASE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_NOU_CASE)));
			items.setFITEM_ORD_SEQ(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ORD_SEQ)));
			items.setFITEM_PRIL_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_PRIL_CODE)));
			items.setFITEM_RE_ORDER_LVL(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_RE_ORDER_LVL)));
			items.setFITEM_RE_ORDER_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_RE_ORDER_QTY)));
			items.setFITEM_S_BRAND_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_S_BRAND_CODE)));
			items.setFITEM_SKU_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_SKU_CODE)));
			items.setFITEM_SKU_SIZ_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_SKU_SIZ_CODE)));
			items.setFITEM_TAX_COM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_TAX_COM_CODE)));
			items.setFITEM_TYPE_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_TYPE_CODE)));
			items.setFITEM_UNIT_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_UNIT_CODE)));
			items.setFITEM_VEN_P_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_VEN_P_CODE)));
			items.setQOH(cursor.getString(cursor.getColumnIndex(dbHelper.FITEMLOC_QOH)));
			
			list.add(items);
			
		}
		
		return list;
	}
	@SuppressWarnings("static-access")
	public String getItemNameByCode(String code){
		
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		String selectQuery = "SELECT * FROM " + dbHelper.TABLE_FITEM +" WHERE "+dbHelper.FITEM_ITEM_CODE+"='"+code+"'" ;
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext()){

			return cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ITEM_NAME));
			
		}
			
		return "";
	}
	
	//getFITEM_NOU_CASE
	
	
	@SuppressWarnings("static-access")
	public String getUnitByCode(String itemCode){
		
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		String selectQuery = "SELECT * FROM " + dbHelper.TABLE_FITEM +" WHERE "+dbHelper.FITEM_ITEM_CODE+"='"+itemCode+"'" ;
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext()){

			return cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_NOU_CASE));
			
		}
			
		return "";
	}
	
	@SuppressWarnings("static-access")
	public ArrayList<ItemFreeIssue> getAllFreeItemNameByRefno(String code) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		ArrayList<ItemFreeIssue> list = new ArrayList<ItemFreeIssue>();
		
		String selectQuery = "SELECT * FROM " + dbHelper.TABLE_FITEM +" WHERE "+dbHelper.FITEM_ITEM_CODE+" in (select itemcode from ffreeItem where refno ='"+code+"')";
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		while(cursor.moveToNext()){
			
			ItemFreeIssue issue =new ItemFreeIssue();
			Items items=new Items();
		
			items.setFITEM_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ID)));
			items.setFITEM_ADD_MATCH(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ADD_MATCH)));
			items.setFITEM_ADD_USER(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ADD_USER)));
			items.setFITEM_AVG_PRICE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_AVG_PRICE)));
			items.setFITEM_BRAND_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_BRAND_CODE)));
			items.setFITEM_GROUP_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_GROUP_CODE)));
			items.setFITEM_ITEM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ITEM_CODE)));
			items.setFITEM_ITEM_NAME(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ITEM_NAME)));
			items.setFITEM_ITEM_STATUS(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ITEM_STATUS)));
			items.setFITEM_MAP_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_MAP_CODE)));
			items.setFITEM_MUST_SALE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_MUST_SALE)));
			items.setFITEM_NOU_CASE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_NOU_CASE)));
			items.setFITEM_ORD_SEQ(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_ORD_SEQ)));
			items.setFITEM_PRIL_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_PRIL_CODE)));
			items.setFITEM_RE_ORDER_LVL(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_RE_ORDER_LVL)));
			items.setFITEM_RE_ORDER_QTY(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_RE_ORDER_QTY)));
			items.setFITEM_S_BRAND_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_S_BRAND_CODE)));
			items.setFITEM_SKU_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_SKU_CODE)));
			items.setFITEM_SKU_SIZ_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_SKU_SIZ_CODE)));
			items.setFITEM_TAX_COM_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_TAX_COM_CODE)));
			items.setFITEM_TYPE_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_TYPE_CODE)));
			items.setFITEM_UNIT_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_UNIT_CODE)));
			items.setFITEM_VEN_P_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FITEM_VEN_P_CODE)));
			
			issue.setItems(items);
			issue.setAlloc("0");
			list.add(issue);
			
		}
		
		return list;
	}
	
}
